
#单表练习  student表
student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

#一对一查询
#身份证
create table idcard(
	cardid varchar(18),
    address varchar(20)
)character set utf8;
alter table idcard add constraint pk_idcard primary key(cardid);
#人
create table person(
	pid int(15),
    pname varchar(30),
    cardid varchar(18)
)character set utf8;
alter table person add constraint pk_person primary key(pid);

alter table person add constraint fk_person_idcard foreign key(cardid) references idcard(cardid);
alter table person add constraint uk_person unique(cardid);


#多对多查询
#部门表
create table dept (
	deptno int(10),
    dname varchar(20),
    loc varchar(20)
)character set utf8;
alter table dept add constraint pk_dept primary  key(deptno);
commit;

insert into dept values(10,'ACCOUNTING','NEW YOKR');
insert into dept values(20,'DESEARCH','DALLAS');
insert into dept values(30,'SALES','CHICAGO');

#员工表
create table emp(
	empno int(4),
    ename varchar(20),
    sal float(10,2),
    deptno int(10)
)character set utf8;
alter table emp add constraint pk_emp primary key(empno);
alter table emp add constraint fk_emp_dept foreign key(deptno) references dept(deptno);

insert into emp values(7369,'SMITH',800,20);
insert into emp values(7499,'ALLEN',1600,30);
insert into emp values(7521,'WARD',1250,30);
insert into emp values(7566,'JONSE',2975,20);
insert into emp values(7782,'CLARK',2450,10);
insert into emp values(7839,'KING',5000,10);
insert into emp values(7788,'SCOTT',3000,20);



#=======================================  多对多[数据表]  =====================================================
#老师信息与学生信息[一个学生对应多个老师,一个老师也可以对应多个学生]
#老师表
create table teacher(
	tid int(8),
	tname varchar(20),
    tsex	   varchar(4),
    tage    int(3)
)character set utf8;
alter table teacher add constraint pk_tacher primary key(tid);

#学生表
create table stu(
	sid int(10),
    sname varchar(20),
    ssex varchar(4),
    sage int(3)
)character set utf8;
alter table stu add constraint pk_tacher primary key(sid);

#中间表，映射老师与学生的多对多关系
create table tea_stu(
	tid int(10),
    sid int(10)
)character set utf8;
alter table tea_stu add constraint fk_treacher foreign key(tid) references teacher(tid);
alter table tea_stu add constraint fk_stu foreign key(sid) references stu(sid);
alter table tea_stu add constraint pk_tea_stu primary key(tid,sid);

#测试数据
begin;
#添加测试数据--l老师表
insert into teacher values(1,'mr-li','男',22);
insert into teacher values(2,'mr-qian','男',24);
insert into teacher values(3,'mr-liu','女',25);
insert into teacher values(4,'ms-han','女',20);
#添加测试数据--学生表
insert into stu values(1,'xiaozhao','男',12);
insert into stu values(2,'xiaozhou','女',11);
insert into stu values(3,'xiaowu','男',9);
insert into stu values(4,'xiaozhang','女',10);
insert into stu values(5,'xiaohuang','男',14);
insert into stu values(6,'xiaozheng','女',17);
insert into stu values(7,'xiaofeng','男',13);
insert into stu values(8,'xiaocheng','女',7);
insert into stu values(9,'xiaojiang','男',7);
insert into stu values(10,'xiaosheng','女',7);
#添加测试数据--映射表
insert into tea_stu values(1,1);insert into tea_stu values(1,5);
insert into tea_stu values(1,7);insert into tea_stu values(2,3);
insert into tea_stu values(2,5);insert into tea_stu values(3,4);
insert into tea_stu values(3,7);insert into tea_stu values(4,2);
insert into tea_stu values(4,9);insert into tea_stu values(3,8);
insert into tea_stu values(2,1);insert into tea_stu values(4,1);
insert into tea_stu values(3,1);insert into tea_stu values(2,2);
insert into tea_stu values(3,2);insert into tea_stu values(1,3);
insert into tea_stu values(4,3);insert into tea_stu values(1,4);
insert into tea_stu values(2,4);insert into tea_stu values(4,6);
insert into tea_stu values(1,6);insert into tea_stu values(2,6);
insert into tea_stu values(1,8);insert into tea_stu values(2,8);
insert into tea_stu values(1,9);insert into tea_stu values(3,9);
insert into tea_stu values(3,10);
commit;


